
-- **************************************************************************
--    Project Name:   运力干线准点明细
--    Job Name:       jms_dm.dm_yl_shipment_trunk_detail_dt
--    Author :        季修魁
--    date：          2023/09/22
--    modify :        季修魁
--    date：          2023/09/22
--    modify :        季修魁      调整回刷周期，7天
--    date：          2023/11/20
-- **************************************************************************
-- **************************************************************************

with shipment_tab as
(select shipment.shipment_no                  as shipment_no              
       ,shipment.shipment_name                as shipment_name
       ,shipment.shipment_state               as shipment_state
       ,shipment.cnt                          as shipment_cnt
       ,shipment.overtime_sub_type_code       as overtime_sub_type_code
       ,stop1.network_code                    as send_network_code
       ,stop1.network_name                    as send_network_name      
       ,shipment.carrier_id                   as carrier_id                        --当前站点承运商ID
       ,shipment.carrier_type                 as carrier_type                      --当前站点承运商类型
       ,shipment.carrier_name                 as carrier_name                      --当前站点承运商全名
       ,shipment.grade                        as grade                             --线路类型 1-省际 2-经济圈 3-省内 4-短驳       
       ,stop1.planned_departure_time          as send_site_planned_departure_time      --当前站点计划发车时间
       ,stop1.app_departure_time              as send_site_app_departure_time          --当前站点app发车时间
       ,stop1.planned_arrival_time            as send_site_planned_arrival_time        --当前站点计划到车时间
       ,stop1.app_arrival_time                as send_site_app_arrival_time            --当前站点app到车时间
       ,stop1.sort_num                        as send_site_sort_num                    --当前站点顺序编号
       ,stop2.network_code                    as next_site_code                        --下一站点编码
       ,stop2.network_name                    as next_site_name                        --下一站点名称
       ,stop2.plan_driving_time               as next_site_plan_driving_time           --下一站规定行驶时间
       ,stop2.actual_driving_time             as next_site_actual_driving_time         --下一站实际行驶时间
       ,stop2.planned_arrival_time            as next_site_planned_arrival_time        --下一站点计划到车时间
       ,stop2.app_arrival_time                as next_site_app_arrival_time            --下一站点app到车时间
       ,stop2.usetime                         as usetime                               --运行时间
       ,stop2.actual_driving_time             as actual_usetime                        --实际运行时间
       ,dim.agent_code                        as agent_code                            --代理区编码
       ,dim.agent_name                        as agent_name                            --代理区名称
from (select shipment_no,
             shipment_state,
             shipment_name,
             id,
             cnt,
             carrier_id,
             carrier_type,
             carrier_name, 
             grade,
             overtime_sub_type_code
       from jms_dwd.dwd_tmsnew_shipment_union_base_dt
      where dt>=date_add('{{ execution_date | cst_ds }}',-15)  and dt <=date_add('{{ execution_date | cst_ds }}',15)
        and table_type=1
      ) shipment
left join (select * from jms_dwd.dwd_tmsnew_shipment_stop_dt where dt='{{ execution_date | cst_ds }}') stop1
  on shipment.shipment_no = stop1.shipment_no and shipment.cnt <> stop1.sort_num 
left join (select * from jms_dwd.dwd_tmsnew_shipment_stop_dt where dt='{{ execution_date | cst_ds }}') stop2
  on stop1.shipment_no = stop2.shipment_no and stop2.sort_num = stop1.sort_num + 1
left join jms_dim.dim_network_whole_massage dim
    on stop1.network_code = dim.code
)

---到车准点率   tms00000014
---发车准点率   tms00000012
---运行准点率   tms00000013

insert overwrite table jms_dm.dm_yl_shipment_trunk_detail_dt partition (dt)
select *
  from (
select case when hour(next_site_app_arrival_time)<9
            then date_sub(to_date(next_site_app_arrival_time),1)
            else to_date(next_site_app_arrival_time)
        end  as date_time
       ,shipment.shipment_no                      
       ,shipment.shipment_name                    
       ,shipment.shipment_state                   
       ,shipment.shipment_cnt                     
       ,shipment.overtime_sub_type_code           
       ,shipment.send_network_code                
       ,shipment.send_network_name                
       ,shipment.carrier_id                       
       ,shipment.carrier_type                     
       ,shipment.carrier_name                     
       ,shipment.grade                            
       ,shipment.send_site_planned_departure_time 
       ,shipment.send_site_app_departure_time     
       ,shipment.send_site_planned_arrival_time   
       ,shipment.send_site_app_arrival_time       
       ,shipment.send_site_sort_num               
       ,shipment.next_site_code                   
       ,shipment.next_site_name                   
       ,shipment.next_site_plan_driving_time      
       ,shipment.next_site_actual_driving_time    
       ,shipment.next_site_planned_arrival_time   
       ,shipment.next_site_app_arrival_time       
       ,shipment.usetime
       ,shipment.actual_usetime
       ,shipment.agent_code                       
       ,shipment.agent_name                       
       ,case when next_site_app_arrival_time is not null then 1 else 0 end  is_shipment_flag
       ,case when (unix_timestamp(next_site_planned_arrival_time)-unix_timestamp(next_site_app_arrival_time)) >= 0 then 1 else 0 end  as is_ontime_flag
       ,'3'    as  stat_type      ---到车准点率
---       ,date_add('{{ execution_date | cst_ds }}',-1) as dt
       ,case when hour(next_site_app_arrival_time)<9 then date_sub(to_date(next_site_app_arrival_time),1)
             else to_date(next_site_app_arrival_time)
        end as dt
  from shipment_tab shipment
 where next_site_app_arrival_time is not null and shipment_state <> 5
   and next_site_app_arrival_time >=concat(date_add('{{ execution_date | cst_ds }}',-7), ' 09:00:00')
   and next_site_app_arrival_time <concat(date_add('{{ execution_date | cst_ds }}',0), ' 09:00:00')

union all 

select  case when hour(next_site_app_arrival_time)<9 then date_sub(to_date(next_site_app_arrival_time),1)
             else to_date(next_site_app_arrival_time)
          END as date_time
       ,shipment.shipment_no                      
       ,shipment.shipment_name                    
       ,shipment.shipment_state                   
       ,shipment.shipment_cnt                     
       ,shipment.overtime_sub_type_code           
       ,shipment.send_network_code                
       ,shipment.send_network_name                
       ,shipment.carrier_id                       
       ,shipment.carrier_type                     
       ,shipment.carrier_name                     
       ,shipment.grade                            
       ,shipment.send_site_planned_departure_time 
       ,shipment.send_site_app_departure_time     
       ,shipment.send_site_planned_arrival_time   
       ,shipment.send_site_app_arrival_time       
       ,shipment.send_site_sort_num               
       ,shipment.next_site_code                   
       ,shipment.next_site_name                   
       ,shipment.next_site_plan_driving_time      
       ,shipment.next_site_actual_driving_time    
       ,shipment.next_site_planned_arrival_time   
       ,shipment.next_site_app_arrival_time       
       ,shipment.usetime
       ,shipment.actual_usetime
       ,shipment.agent_code                       
       ,shipment.agent_name
       ,case when next_site_app_arrival_time is not null then 1 else 0 end  is_shipment_flag
       ,case when actual_usetime <=usetime then 1 else 0 end  as is_ontime_flag   
       ,'2'    as  stat_type      ---运行准点率
       ,case when hour(next_site_app_arrival_time)<9 then date_sub(to_date(next_site_app_arrival_time),1)
             else to_date(next_site_app_arrival_time)
        end as dt
  from shipment_tab shipment
 where next_site_app_arrival_time is not null and shipment_state <> 5
   and next_site_app_arrival_time >=concat(date_add('{{ execution_date | cst_ds }}',-7), ' 09:00:00')
   and next_site_app_arrival_time <concat(date_add('{{ execution_date | cst_ds }}',0), ' 09:00:00')

union all 

select case when hour(send_site_planned_departure_time)<9 then date_sub(to_date(send_site_planned_departure_time),1)
            else to_date(send_site_planned_departure_time)
        end as date_time
       ,shipment.shipment_no                      
       ,shipment.shipment_name                    
       ,shipment.shipment_state                   
       ,shipment.shipment_cnt                     
       ,shipment.overtime_sub_type_code           
       ,shipment.send_network_code                
       ,shipment.send_network_name                
       ,shipment.carrier_id                       
       ,shipment.carrier_type                     
       ,shipment.carrier_name                     
       ,shipment.grade                            
       ,shipment.send_site_planned_departure_time 
       ,shipment.send_site_app_departure_time     
       ,shipment.send_site_planned_arrival_time   
       ,shipment.send_site_app_arrival_time       
       ,shipment.send_site_sort_num               
       ,shipment.next_site_code                   
       ,shipment.next_site_name                   
       ,shipment.next_site_plan_driving_time      
       ,shipment.next_site_actual_driving_time    
       ,shipment.next_site_planned_arrival_time   
       ,shipment.next_site_app_arrival_time       
       ,shipment.usetime
       ,shipment.actual_usetime            
       ,shipment.agent_code                       
       ,shipment.agent_name
       ,case when send_site_app_departure_time is not null then 1 else 0 end  is_shipment_flag       
       ,case when send_site_app_departure_time is not null and send_site_sort_num =  1 
              and unix_timestamp(send_site_planned_departure_time) - unix_timestamp(send_site_app_departure_time) >= 0 then 1
             when send_site_app_departure_time is not null and send_site_sort_num <> 1 
              and unix_timestamp(send_site_planned_departure_time) - unix_timestamp(send_site_app_departure_time) >= 0 then 1
             when send_site_app_departure_time is not null and send_site_sort_num <> 1 and unix_timestamp(send_site_app_departure_time) > unix_timestamp(send_site_planned_departure_time)
              and ceil(unix_timestamp(send_site_app_departure_time) - unix_timestamp(send_site_planned_departure_time)) - 
              ceil(unix_timestamp(send_site_app_arrival_time) - unix_timestamp(send_site_planned_arrival_time)) <= 0 then 1
		else 0 end as is_ontime_flag  
       ,'1'    as  stat_type      ---发车准点率
       ,case when hour(send_site_planned_departure_time)<9 then date_sub(to_date(send_site_planned_departure_time),1)
             else to_date(send_site_planned_departure_time)
        end as dt
  from shipment_tab shipment
 where send_site_planned_departure_time is not null
   and send_site_planned_departure_time >=concat(date_add('{{ execution_date | cst_ds }}',-7), ' 09:00:00')
   and send_site_planned_departure_time <concat(date_add('{{ execution_date | cst_ds }}',0), ' 09:00:00')      
   and shipment_state in (3, 4) --3在途，4完成
   and (overtime_sub_type_code <> 182 or overtime_sub_type_code is null) --过滤循坏袋加班
) t
distribute by dt,pmod(hash(rand()),10)
;